package com.syl.starter.itools.framework.utils;

import com.syl.starter.itools.base.utils.CloseUtils;
import com.syl.starter.itools.base.utils.LogUtils;
import com.syl.starter.itools.common.utils.*;
import com.syl.starter.itools.framework.annotations.ExcelField;
import com.syl.starter.itools.framework.enums.ExcelTypeEnum;
import com.syl.starter.itools.framework.utils.config.ExcelBaseConfig;
import com.syl.starter.itools.framework.utils.config.ExcelOutputConfig;
import lombok.Getter;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.nio.file.Files;
import java.util.*;

/**
 * excel操作工具类 TODO 待完善
 *
 * @author syl
 * @create 2018-10-30 19:00
 **/
public class ExcelUtils {
    private static final Logger LOG = LoggerFactory.getLogger(ExcelUtils.class);

    private static final int PADDING = 4; // 单元格内容间距
    private static final int FACTOR = 256; // 宽度必须*的系数
    private static final int MAX_LENGTH = 50; // 标题最大展示长度
    private InputStream fileInputStream = null;
    @Getter
    private File excelFile;
    private ExcelTypeEnum excelTypeEnum;
    private Workbook workbook = null;
    private List<Map<String, Object>> dataList = new ArrayList<>();

    private ExcelUtils() {}

    /**
     * 按excel路径构建
     * @param excelPath 目标的excel路径
     */
    public ExcelUtils(String excelPath){
        this(new File(excelPath));
    }

    /**
     * 按文件夹和文件名类型构造输出对象
     * @param dirPath 文件夹名称
     * @param fileName 导出文件名称
     * @param excelTypeEnum excel类型
     * @param dataList 待导出数据
     */
    public ExcelUtils(String dirPath, String fileName, ExcelTypeEnum excelTypeEnum, List<Map<String, Object>> dataList) {
        this(new File(dirPath + File.separator + fileName + excelTypeEnum.getSuffix()), dataList);
    }

    /**
     * 按excel类型构造输出对象 自动构建临时文件
     * @param excelTypeEnum excel类型
     * @param dataList 待输出数据
     */
    public ExcelUtils(ExcelTypeEnum excelTypeEnum, List<Map<String, Object>> dataList) throws IOException {
        this(Files.createTempFile("temp", excelTypeEnum.getSuffix()).toFile(), dataList);
    }

    /**
     * 构造输出对象
     * @param excelFile 待输出的excel文件
     * @param dataList 数据
     */
    public ExcelUtils(File excelFile, List<Map<String, Object>> dataList) {
        this(excelFile);
        this.dataList = dataList;
    }

    /**
     * 构造一个Workbook
     * @param excelFile 目标的excel文件
     */
    @SneakyThrows
    public ExcelUtils(File excelFile) {
        Assert.notNull(excelFile, "file不能为空");
        this.excelFile = excelFile;
        String fileName = excelFile.getName();
        if (fileName.endsWith(ExcelTypeEnum.XLS.getSuffix())){
            excelTypeEnum = ExcelTypeEnum.XLS;
        } else if(fileName.endsWith(ExcelTypeEnum.XLSX.getSuffix())){
            excelTypeEnum = ExcelTypeEnum.XLSX;
        }
    }

    /**
     * 创建一个workbook
     * @param read 是否读
     */
    @SneakyThrows
    private void initWorkbook(boolean read){
        if (read) {
            fileInputStream = new FileInputStream(this.excelFile);
            if (ExcelTypeEnum.XLS == excelTypeEnum){
                //2003
                workbook = new HSSFWorkbook(fileInputStream);
            } else if (ExcelTypeEnum.XLSX == excelTypeEnum){
                //2007
                workbook = new XSSFWorkbook(fileInputStream);
            }
        }
        else {
            if (ExcelTypeEnum.XLS == excelTypeEnum){
                workbook = new HSSFWorkbook();
            } else if (ExcelTypeEnum.XLSX == excelTypeEnum){
                workbook = new XSSFWorkbook();
            }
        }
    }

    /**
     * 默认读取Excel文件第0个sheet，第0行为标题，第0列为开始列
     * @return 以表格标题为key的map对象集合
     */
    public List<Map<String, Object>> readExcelToMapList(){
        return readExcelToMapList(0);
    }

    /**
     * 读取Excel文件并转换为 map list 对象 默认读取sheet中的第0行 第0列
     * @param startSheet 开始sheet
     * @return 以表格标题为key的map对象集合
     */
    public List<Map<String, Object>> readExcelToMapList(int startSheet){
        return this.readExcelToMapList(startSheet, 0, 0);
    }

    /**
     * 读取Excel文件并转换为 map list 对象 默认读取第0 sheet
     * @param startRow 开始行
     * @param startColumn 开始列
     * @return 以表格标题为key的map对象集合
     */
    public List<Map<String, Object>> readExcelToMapList(int startRow, int startColumn){
        return this.readExcelToMapList(0, startRow, startColumn);
    }

    /**
     * 读取Excel文件并转换为 map list 对象
     * @param startSheet 开始sheet
     * @param startRow 开始行
     * @param startColumn 开始列
     * @return 以startRow为key的map对象集合
     */
    public List<Map<String, Object>> readExcelToMapList(int startSheet, int startRow, int startColumn){
        this.initWorkbook(true);
        List<Map<String, Object>> mapList = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(startSheet);
        Row titleRow = sheet.getRow(startRow);
        for (int i = startRow + 1; i <= sheet.getLastRowNum(); i++){ //从第二行开始读取数据，第一行数据为key,
            Map<String, Object> map = new LinkedHashMap<>();
            Row row = sheet.getRow(i); //第2行开始是数据行
            if (row == null) continue;
            for (int j = startColumn; j < row.getLastCellNum(); j++){
                String title = String.valueOf(TypeConvertUtils.convertCellValue(titleRow.getCell(j)));
                Object value = TypeConvertUtils.convertCellValue(row.getCell(j));
                // 防止空内容读取成空字符串
                if (value == null) map.put(title, null);
                else map.put(title, StringUtils.isEmpty(StringUtils.toString(value)) ? null : value);
            }
            if (!ListUtils.isAllEmpty(map.values())) mapList.add(map);
        }
        CloseUtils.closeAll(fileInputStream);
        return mapList;
    }

    /**
     * 读取Excel文件并转换为java对象 默认读取Excel文件第0个sheet，第0行为标题，第0列为开始列
     * @param cz 待转换的java类
     * @return java bean list
     */
    public <BEAN> List<BEAN> readExcelToMapList(Class<BEAN> cz){
        return readExcelToMapList(0, cz);
    }

    /**
     * 读取Excel文件并转换为java对象 默认读取sheet中的第0行 第0列
     * @param startSheet 开始sheet
     * @param cz 待转换的java类
     * @return java bean list
     */
    public <BEAN> List<BEAN> readExcelToMapList(int startSheet, Class<BEAN> cz){
        return this.readExcelToMapList(startSheet, 0, 0, cz);
    }

    /**
     * 读取Excel文件并转换为java对象 默认读取第0 sheet
     * @param startRow 开始行
     * @param startColumn 开始列
     * @param cz 待转换的java类
     * @return java bean list
     */
    public <BEAN> List<BEAN> readExcelToMapList(int startRow, int startColumn, Class<BEAN> cz){
        return this.readExcelToMapList(0, startRow, startColumn, cz);
    }

    /**
     * 读取Excel文件并转换为java对象
     * @param startSheet 开始sheet
     * @param startRow 开始行
     * @param startColumn 开始列
     * @param cz 待转换的java类
     * @return java bean list
     */
    @SneakyThrows
    public <BEAN> List<BEAN> readExcelToMapList(int startSheet, int startRow, int startColumn, Class<BEAN> cz){
        List<Map<String, Object>> mapList = this.readExcelToMapList(startSheet, startRow, startColumn);
        List<BEAN> resList = new ArrayList<>(mapList.size());
        for (Map<String, Object> map : mapList) {
            Set<String> keySet = map.keySet();
            BEAN bean = ReflectUtils.instantiation(cz);
            Field[] privateField = ReflectUtils.getPrivateField(cz);
            Map<String, Field> fieldMap = new HashMap<>();
            for (Field field : privateField) {
                ExcelField excelFieldAnnotation = field.getAnnotation(ExcelField.class);
                if (excelFieldAnnotation == null) continue;
                String head = excelFieldAnnotation.value();
                fieldMap.put(head, field);
            }
            for (String key : keySet) {
                key = StringUtils.trim(key);
                Object value = map.get(key);
                if (value == null) continue;
                Field field = fieldMap.get(key);
                if (field == null) continue;
                String fieldName = field.getName();
                Type type = field.getGenericType();
                ReflectUtils.setFieldValue(bean, fieldName, TypeConvertUtils.convertStringToObject(String.valueOf(value), type));
            }
            resList.add(bean);
        }
        return resList;
    }

    /**
     * 把数据输出到指定Excel文件中 自动关闭所有流
     * @param excelOutputConfig excel输出配置
     * @return 数据是否写入成功
     */
    public boolean outputExcel(ExcelOutputConfig excelOutputConfig){
        return this.outputExcel(excelOutputConfig, true);
    }

    /**
     * 把数据输出到指定Excel文件中
     * @param excelOutputConfig excel输出配置
     * @param close 是否立即关闭流
     * @return 数据是否写入成功
     */
    public boolean outputExcel(ExcelOutputConfig excelOutputConfig, boolean close){
        this.initWorkbook(false);
        ExcelOutputConfig.ExcelTitle excelTitle = excelOutputConfig.getExcelTitle();
        ExcelOutputConfig.ExcelHead excelHead = excelOutputConfig.getExcelHead();
        ExcelOutputConfig.ExcelBody excelBody = excelOutputConfig.getExcelBody();
        int sheetIndex = excelOutputConfig.getSheetIndex();
        int startRow = excelOutputConfig.getStartRow();
        int startColumn = excelOutputConfig.getStartColumn();
        // 从空的workbook中初始化sheet
        workbook.createSheet(excelOutputConfig.getSheetName());
        if (excelTitle != null) {
            this.initTitle(sheetIndex, startRow, startColumn, excelTitle);
            startRow = startRow + excelTitle.getRow() ;
        }
        this.initHead(sheetIndex, startRow, startColumn, excelHead);
        startRow = startRow + 1;
        this.initBody(sheetIndex, startRow, startColumn, excelBody);
        FileOutputStream outputStream = null;
        Workbook workbook = this.workbook;
        try {
            outputStream = new FileOutputStream(this.excelFile);
            workbook.write(outputStream);
        } catch (IOException e) {
            LogUtils.error(LOG, e);
            return false;
        }finally {
            if (close) CloseUtils.closeAll(workbook, outputStream);
        }
        return true;
    }

    /**
     * 初始化excel标题
     * @param sheetIndex 目标sheet下标 从0开始
     * @param startRow 开始行
     * @param startColumn 开始列
     * @param excelTitle 标题样式配置
     */
    protected void initTitle(int sheetIndex, int startRow, int startColumn, ExcelOutputConfig.ExcelTitle excelTitle){
        int endRow = excelTitle.getRow() -1;
        int endColumn = startColumn + this.dataList.get(0).keySet().size() -1;
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(startRow, endRow, startColumn, endColumn);
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.addMergedRegion(region);
        boolean b = true; // 标记 只对第一项cell 设置值 其余设置边框
        for (int i = 0; i <= endRow ; i++) {
            Row row = sheet.createRow(i);
            for (int j = startColumn; j <= endColumn; j++) {
                Cell cell = row.createCell(j);
                if (b){
                    cell.setCellValue(excelTitle.getTitle());
                }
                this.setCellStyle(excelTitle, cell);
                b = false;
            }
        }
    }

    /**
     * 初始化表格标题
     * @param sheetIndex 目标sheet下标 从0开始
     * @param startRow 开始行
     * @param startColumn 开始列
     * @param excelHeads 每一个表格标题都可单独设置 如果个数不符合则使用默认格式
     */
    protected void initHead(int sheetIndex, int startRow, int startColumn, ExcelOutputConfig.ExcelHead... excelHeads) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.createRow(startRow);
        Map<String, Object> map = this.dataList.get(0);
        Set<String> keySet = map.keySet();
        ExcelBaseConfig temp = ListUtils.isEmpty(excelHeads) || excelHeads.length != keySet.size() ? new ExcelOutputConfig.ExcelHead() : null;
        int index = 0;
        int j = startColumn;
        for (String title : keySet) {
            Cell cell = row.createCell(j);
            cell.setCellValue(title);
            if (temp != null) this.setCellStyle(temp, cell);
            else this.setCellStyle(excelHeads[index], cell); //
            int length = StringUtils.toString(map.get(title)).getBytes().length;
            int tempLength = Math.max(length, title.length());
            // 设置表头大小自适应宽度
            sheet.setColumnWidth(j, (Math.min(tempLength, MAX_LENGTH) + PADDING) * FACTOR);
            j++;
            index++;
        }
    }

    /**
     * 表格内容
     * @param sheetIndex 目标sheet下标 从0开始
     * @param startRow 开始行
     * @param startColumn 开始列
     * @param excelBody 表格内容配置类
     */
    protected void initBody(int sheetIndex, int startRow, int startColumn, ExcelOutputConfig.ExcelBody excelBody) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int index = startRow;
        for (Map<String, Object> map : dataList) {
            Row row = sheet.createRow(index);
            int j = startColumn;
            for (String key : map.keySet()) {
                Cell cell = row.createCell(j);
                cell.setCellValue(convertValueToString(map.get(key)));
                this.setCellStyle(excelBody, cell);
                j++;
            }
            index++;
        }
    }

    /**
     * 设置单元格样式
     * @param config excel基础配置类
     * @param cell 单元格
     */
    private void setCellStyle(ExcelBaseConfig config, Cell cell){
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(config.isBold());
        font.setFontName(config.getFontName());
        font.setColor(config.getColor().index);
        style.setFont(font);
//        style.setFillBackgroundColor(config.getBackground().index); //设置背景色
        style.setFillForegroundColor(config.getBackground().index); // 改为设置前景色后才达到想要的效果
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(config.getHorizontalAlignment());
        style.setVerticalAlignment(config.getVerticalAlignment());
        style.setWrapText(config.isWrapText());
        if (config.isBorder()) {
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
        }
        cell.setCellStyle(style);
    }

    // TODO 待优化
    private String convertValueToString(Object object){
        if (object instanceof Date) {
            return new DateUtils((Date) object).format();
        }
        return String.valueOf(object);
    }


    /**
     * 是否合并单元格 默认第一个sheet
     * @param startRow 开始行
     * @param endColumn 结束列
     */
    public boolean isMergeCells(int startRow, int endColumn){
        return isMergeCells(0, startRow, endColumn);
    }

    /**
     * 是否合并单元格
     * @param sheetIndex 第几个sheet 下标从0开始
     * @param startRow 开始行
     * @param endColumn 结束列
     * @return 该单元格为合并列
     */
    public boolean isMergeCells(int sheetIndex, int startRow, int endColumn){
        return getRowNumber(sheetIndex, startRow, endColumn) > 0;
    }

    /**
     * 获取目标单元格行数 默认第一个sheet
     * @param startRow 开始行
     * @param endColumn 结束列
     * @return 目标单元格范围行数
     */
    public int getRowNumber(int startRow, int endColumn){
        return getRowNumber(0, startRow, endColumn);
    }

    /**
     * 获取目标单元格行数
     * @param sheetIndex 第几个sheet 下标从0开始
     * @param startRow 开始行
     * @param endColumn 结束列
     * @return 目标单元格范围行数
     */
    public int getRowNumber(int sheetIndex, int startRow, int endColumn){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(startRow);
        Cell cell = row.getCell(endColumn);
        int firstRowNum = cell.getRow().getSheet().getFirstRowNum();
        int lastRowNum = cell.getRow().getSheet().getLastRowNum();
        return firstRowNum + lastRowNum;
    }

}
